﻿#region Creditos

/* Autor: Edilberto Sánchez Forero	*/
/* Email: esanchezfo@gmail.com		*/
/* Licencia: LGPL, Software Libre	*/
/* País: Colombia, Bogotá			*/

#endregion

#region Librerias

using System;
using System.Data;
using System.Linq;
using System.IO;
using System.Text;
using System.Xml;

using ESF.General;
using System.Collections.Generic;
using ESF.General.Entity;
using System.Diagnostics;
using System.Configuration;
using System.Data.OleDb;
using System.Data.SqlClient;

#endregion


namespace ESF.Data
{
	/// <summary>
	/// Contiene las funciones de recuperacion de informacion de la base de datos
	/// </summary>
	public class DbReader : DbCommon
	{
		#region Constructores
		
		private DbReader () { }
		
		
		public DbReader (string pConnectionName, params string [] pParams) : base (pConnectionName, pParams) {}
		

		public DbReader (ConnectionStringSettings pSettings) : base (pSettings) {}

		#endregion


		#region Instanciacion para manejar excel

		public static DbReader CreateDbExcelReader (string pXLSFilename)
		{
			string connectionstring;


			if (!File.Exists (pXLSFilename))
			{
				throw new FileNotFoundException (string.Format ("The file {0} has not found", pXLSFilename), pXLSFilename);
			}


			switch (Path.GetExtension (pXLSFilename).ToLower ())
			{
				case ".xls":
					connectionstring = string.Format ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\";", pXLSFilename);
					break;
				case ".xlsx":
					connectionstring = string.Format ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\";", pXLSFilename);
					break;
				case ".xlsb":
					connectionstring = string.Format ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\";", pXLSFilename);
					break;
				default:
					throw new NotSupportedException (string.Format ("This is not a supported Excel file format: {0}", Path.GetExtension (pXLSFilename)));
			}

			return new DbReader (
				new ConnectionStringSettings () { 
					Name = "Excel", 
					ConnectionString=connectionstring,
					ProviderName = "System.Data.OleDb" } );
		}

		#endregion
	
	
		#region Listados Jerarquicos
		
		public List<LeafTreeItem<O>> GetTree<O> (IDbCommand cmd, DataHelper.GetObjectFromDataRow<O> pConvertTo, string pParentIDColumnName, string pIDColumnName) where O : IEquatable <O>
		{
			DataTable table;
			
			List<LeafTreeItem<O>> tree;
			
			LeafTreeItem<O> mainNode;
			
			
			table = GetFirstDataTable (cmd);
			
			tree = new List<LeafTreeItem<O>> ();
			
			// Se detectan los padres, aquellos que tienen su parentid nulo
			foreach (DataRow r in table.Select (string.Format ("{0} IS NULL", pParentIDColumnName)))
			{
				mainNode = new LeafTreeItem<O> (pConvertTo (r, string.Empty));
			
				GetTreeAddChildren<O> (table, ref mainNode, pConvertTo, pParentIDColumnName, pIDColumnName, r [pIDColumnName].ToString ());
			
				tree.Add (mainNode);
			}
			
			return tree;
		}
		
		
		private void GetTreeAddChildren<O> (DataTable pTable, ref LeafTreeItem<O> pParentNode, DataHelper.GetObjectFromDataRow<O> pConvertTo, string pParentIDColumnName, string pIDColumnName, string pParentIDValue) where O : IEquatable<O>
		{
			LeafTreeItem<O> currentNode;
			
		
			foreach (DataRow r in pTable.Select (string.Format ("{0} = {1}", pParentIDColumnName, pParentIDValue)))
			{
				currentNode = new LeafTreeItem<O> (pConvertTo (r, string.Empty));
				
				GetTreeAddChildren<O> (pTable, ref currentNode, pConvertTo, pParentIDColumnName, pIDColumnName, r [pIDColumnName].ToString ());
				
				pParentNode.Children.Add (currentNode);
			}
		}
		
		
		#endregion
	
	
		#region Get List
				
		public List<BasicReference> GetListBasicReference (IDbCommand pCmd, string pPrefix = "", string pListLoading = "")
		{
			return GetList<BasicReference> (pCmd, DataHelper.ConvertToBasicReference<BasicReference>, pPrefix, pListLoading);
		}


		public List<string> GetListStringVector (IDbCommand pCmd)
		{
			return GetList<string> (pCmd, DataHelper.BasicVectorConverter, string.Empty);
		}
		

		public List<O> GetList<O> (IDbCommand pCmd, DataHelper.GetObjectFromDataReader<O> pConvertTo, string pPrefix = "", string pListLoading = "")
		{
			return GetReader<O> (pCmd, pConvertTo, pPrefix, pListLoading).ToList ();
		}


		public List<O> GetPagginableList<O>(IDbCommand pCmd, DataHelper.GetObjectFromDataReader<O> pConvertTo, string pPrefix = null, string pListLoading = "") where O : IPagginableInfo
		{
			return ExecutePagginableReader<O>(pCmd, pConvertTo, pPrefix, pListLoading).ToList ();
		}


		#endregion

		
		#region Get Browse

		public BrowseResults<O> GetBrowse<O>(IDbCommand pCmd, DataHelper.GetObjectFromDataReader<O> pConvertTo, PaggingInfo pPageInfo, string pPrefix = "", string pListLoading = "") where O : IPagginableInfo
		{
			BrowseResults<O> lstTemp;

			Stopwatch sw = new Stopwatch ();

			sw.Start ();


			lstTemp = new BrowseResults<O> ();

			lstTemp.Results = GetPagginableList<O> (pCmd, pConvertTo, pPrefix, pListLoading);
			
			if (pPageInfo.PageSize <= 0)
			{
				pPageInfo.PageSize = 10;
			}

			if (DataHelper.IsEmpty (lstTemp.Results))
			{
				pPageInfo.RowCount = 0;
			}
			else
			{
				pPageInfo.RowCount = lstTemp.Results.First().calVirtualRowCount;
			}
			
			lstTemp.PaggingInfo = pPageInfo;
			
			sw.Stop ();

			lstTemp.calProcessingDurationms = sw.ElapsedMilliseconds;

			return lstTemp;
		}
				
		#endregion
	
	
		#region Generico Lectura
		
		public DataTable GetSchemaTables ()
		{
			OleDbConnection cnn = null;


			if (ProviderName == "System.Data.OleDb")
			{
				using (cnn = CreateConnection () as OleDbConnection)
				{
					cnn.Open ();

					return cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
				}
			}
			else
			{
				throw new NotSupportedException (string.Format ("GetSchemaTables is not supported for {0}", ProviderName));
			}
		}


		public DataRow GetFirstRow (string pCommandSQL)
		{
			return DataHelper.GetFirstRow (GetDataSet (pCommandSQL));
		}
		
			
		public DataTable GetFirstTable (string pCommandSQL)
		{
			return DataHelper.GetFirstDataTable (GetDataSet (pCommandSQL));
		}
		
		
		public DataSet GetDataSet (string pCommandSQL)
		{
			IDbCommand dbcComando;
			
			
			dbcComando = CreateCommand ();
			dbcComando.CommandText = pCommandSQL;
			
			
			return GetDataSet (dbcComando);
		}


		public bool HasRows (IDbCommand pCommand)
		{
			return (GetFirstRow (pCommand) != null);
		}
		
			
		public DataRow GetFirstRow (IDbCommand pCommand)
		{
			return DataHelper.GetFirstRow (GetDataSet (pCommand));
		}
		
		
		public DataTable GetFirstTableSP (string pSPName)
		{
			return GetFirstDataTable (CreateSPCommand (pSPName));
		}
		
		
		public DataTable GetFirstDataTable (IDbCommand pCommand)
		{
			return DataHelper.GetFirstDataTable (GetDataSet (pCommand));
		}
			
		/// <summary>
		/// Ejecuta una sentencia de consulta, SELECT, en la base de datos para retornar una tabla
		/// </summary>
		/// <param name="_ConnectionName">Nombre del proveedor de la BD del archivo de configuración</param>
		/// <param name="pCommand">Comando SQL a ser ejecutado</param>
		/// <param name="intIndicePagina">Indice de la pagina a solicitar, arranca desde cero (0)</param>
		/// <param name="intNumeroRegistrosPagina">Numero de registros a mostrar por pagina, si se diligencia cero (0) no te tiene en cuenta</param>
		/// <returns>Las Tablas obtenidas</returns>
		public DataSet GetDataSet (IDbCommand pCommand)
		{
			StringBuilder stbLog = new StringBuilder ();

			Stopwatch stopWatch = new Stopwatch(); 
			
			IDbDataAdapter sdcDbDataAdapter;

			DataSet dtsSet;


			StartExec (stbLog, stopWatch);
			

			dtsSet = new DataSet ();
			
			sdcDbDataAdapter = CreateDataAdapter ();
			
			pCommand.Connection = null;
			
			try
			{
				RegCmdExec (stbLog, pCommand);
					
				using (pCommand.Connection = CreateConnection ())
				{
					pCommand.Connection.Open ();
				
					AddNotDefinedParametersAsNull (pCommand);
					AdjustParametersCommandQuestionMark (pCommand);
	
					sdcDbDataAdapter.SelectCommand = pCommand;
				
					sdcDbDataAdapter.Fill (dtsSet);

					if (DataHelper.IsEmpty (dtsSet)) { dtsSet = null; }

					return dtsSet;
				}
			}
			catch (Exception pEx)
			{
				throw new DbGenericException(ProviderName, pCommand, pEx);
			}
			finally
			{
				EndExec(stbLog, stopWatch); 
				
				Stop(pCommand, ProviderName, null);
			}
		}
		
		#endregion


		#region Get One

		public O Get<O>(IDbCommand pCmd, DataHelper.GetObjectFromDataReader<O> pConvertTo, string pPrefix = "", string pListLoading = "")
		{
			return GetReader<O>(pCmd, pConvertTo, pPrefix, pListLoading).FirstOrDefault();
		}

		#endregion

		
		#region Especializado de Lectura
		
		public bool GetBool (IDbCommand pCommand, bool pNullValue = false)
		{
			return GetScalar<bool> (pCommand, delegate (object pValue) { if (pValue == null || pValue == DBNull.Value) { return pNullValue; } else { return TextHelper.GetBool (pValue.ToString ()); } } );
		}
		
		
		public bool? GetNullableBool (IDbCommand pCommand)
		{
			return GetScalar<bool?>(pCommand, delegate(object pValue) { if (pValue == null || pValue == DBNull.Value) { return null; } else { return TextHelper.GetBool(pValue.ToString()); } });
		}
		
		
		public string GetString (IDbCommand pCommand)
		{
			return GetScalar<string> (pCommand);
		}


		public Dictionary<string, A> GetDictionary <A> (IDbCommand pCommand)
		{
			return GetDictionary <A> (pCommand, null, null);
		}


		public Dictionary<string, A> GetDictionary <A> (IDbCommand pCommand, string pKeyColumnName, string pValueColumnName)
		{
			IDataReader dbrReader;

			
			dbrReader = null;

			pCommand.CommandTimeout = 0;

			Dictionary<string, A> result;

			StringBuilder stbLog = new StringBuilder ();
			
			Stopwatch stopWatch = new Stopwatch ();


			result = new Dictionary<string,A> ();
			
			
			try
			{
				RegCmdExec(stbLog, pCommand); 

				using (pCommand.Connection = CreateConnection ())
				{
					try
					{
						pCommand.Connection.Open ();
					}
					catch (Exception pEx)
					{
						EndExec(stbLog, stopWatch);

						Stop(pCommand, ProviderName, null);

						throw new DbGenericException(ProviderName, pCommand, pEx);
					}			
				
					AddNotDefinedParametersAsNull (pCommand);
					AdjustParametersCommandQuestionMark (pCommand);

					using (dbrReader = pCommand.ExecuteReader(CommandBehavior.CloseConnection))
					{
						if (string.IsNullOrEmpty (pKeyColumnName) || string.IsNullOrEmpty (pValueColumnName))
						{
							while (dbrReader.Read())
							{
								result.Add (dbrReader.GetString (0), (A) dbrReader.GetValue (1));
							}
						}
						else
						{
							while (dbrReader.Read())
							{
								result.Add (dbrReader.GetString (dbrReader.GetOrdinal (pKeyColumnName)), (A) dbrReader.GetValue (dbrReader.GetOrdinal (pValueColumnName)));
							}
						}

						return result;
					}
				}
			}
			catch (Exception excEx)
			{
				throw new DbGenericException (ProviderName, pCommand, excEx);
			}
			finally
			{
				EndExec (stbLog, stopWatch);
				
				Stop(pCommand, ProviderName, null);
			}
		}


		public IEnumerable<O> ExecutePagginableReader<O>(IDbCommand pCommand, DataHelper.GetObjectFromDataReader<O> pDelDataTransformer, string pPrefix = "", string pListLoading = "") where O : IPagginableInfo
		{
			StringBuilder stbLog = new StringBuilder();

			Stopwatch stopWatch = new Stopwatch(); 
			
			IDataReader dbrReader = null;

			bool pasFirst = false;

			O current;


			try
			{
				RegCmdExec(stbLog, pCommand); 

				using (pCommand.Connection = CreateConnection ())
				{
					try
					{
						pCommand.Connection.Open ();
					}
					catch (Exception pEx)
					{
						EndExec(stbLog, stopWatch);

						Stop(pCommand, ProviderName, null);

						throw new DbGenericException(ProviderName, pCommand, pEx);
					}			
				
					AddNotDefinedParametersAsNull (pCommand);
					AdjustParametersCommandQuestionMark (pCommand);

					using (dbrReader = pCommand.ExecuteReader(CommandBehavior.CloseConnection))
					{					
						while (dbrReader.Read())
						{
							current = pDelDataTransformer (dbrReader, pPrefix, pListLoading);
					
							if (!pasFirst)
							{
								current.calVirtualRowCount = DataHelper.GetInt (dbrReader, DataHelper.COLUMNCOUNTNAME, -1);
						
								pasFirst = true;
							}
					
							yield return current;
						}
					}
				}
			}
			finally
			{
				EndExec(stbLog, stopWatch);

				Stop(pCommand, ProviderName, null);
			}
		}


		public IEnumerable<O> GetReader<O>(IDbCommand pCommand, DataHelper.GetObjectFromDataReader<O> pDelDataTransformer, string pPrefix = "", string pListLoading = "")
		{	
			StringBuilder stbLog = new StringBuilder();

			Stopwatch stopWatch = new Stopwatch(); 
			
			IDataReader dbrReader = null;

			int countTransform = 1;
			

			try
			{
				RegCmdExec(stbLog, pCommand); 

				using (pCommand.Connection = CreateConnection ())
				{
					try
					{
						pCommand.Connection.Open ();
					}
					catch (Exception pEx)
					{
						EndExec(stbLog, stopWatch);

						Stop(pCommand, ProviderName, null);

						throw new DbGenericException(ProviderName, pCommand, pEx);
					}			
				
					AddNotDefinedParametersAsNull (pCommand);
					AdjustParametersCommandQuestionMark (pCommand);

					using (dbrReader = pCommand.ExecuteReader ())
					{
						while (dbrReader.Read())
						{
							if (Log.IsDebugLogEnabled)
							{
								stbLog.AppendFormat (" - Transforming row {0} in type {1}", countTransform, typeof (O));
								countTransform ++;

								stbLog.AppendLine ();
							}
				
							yield return pDelDataTransformer(dbrReader, pPrefix, pListLoading); 
						}
					}
				}
			}
			finally
			{
				EndExec (stbLog, stopWatch);

				Stop(pCommand, ProviderName, null);
			}
		}


		public T GetScalar<T>(IDbCommand pCommand, Func<object, T> pDelDataTransformer = null)
		{
			StringBuilder stbLog = new StringBuilder();

			Stopwatch stopWatch = new Stopwatch();

			object result;


			try
			{
				RegCmdExec(stbLog, pCommand);

				using (pCommand.Connection = CreateConnection())
				{
					try
					{
						pCommand.Connection.Open();
					}
					catch (Exception pEx)
					{
						EndExec(stbLog, stopWatch);

						Stop(pCommand, ProviderName, null);

						throw new DbGenericException(ProviderName, pCommand, pEx);
					}

					AddNotDefinedParametersAsNull(pCommand);
					AdjustParametersCommandQuestionMark(pCommand);

					result = pCommand.ExecuteScalar ();
					
					if (pDelDataTransformer != null)
					{
						return pDelDataTransformer (result);
					}
					else
					{
						if (result == null || result == DBNull.Value)
						{
							return default (T);
						}
						else
						{
							try
							{
								return (T) result;
							}
							catch (InvalidCastException pEx)
							{
								throw new DbGenericException (ProviderName, 
									string.Format ("The value is not of type required, the result was {0}", 
									(result == null ? "NULL" : result.GetType ().Name)), 
									pCommand, pEx);
							}
						} 
					}					
				}
			}
			finally
			{
				EndExec(stbLog, stopWatch);

				Stop(pCommand, ProviderName, null);
			}
		}


		public string GetMSSQLXML(IDbCommand pCommand)
		{
			return GetMSSQLXML<string> (pCommand, 
				delegate (XmlReader pReader)
				{
					StringBuilder result = new StringBuilder();


					pReader.Read ();
					
					while (!pReader.EOF)
					{
						result.Append (pReader.ReadOuterXml());
					}

					return result.ToString();
				});
		}


		public T GetMSSQLXML<T>(IDbCommand pCommand, Func<XmlReader, T> pDelDataTransformer = null)
		{
			StringBuilder stbLog = new StringBuilder();

			Stopwatch stopWatch = new Stopwatch();

			SqlCommand sqlCommand;

			XmlReader xmlReader;


			try
			{
				RegCmdExec(stbLog, pCommand);

				using (pCommand.Connection = CreateConnection())
				{
					try
					{
						pCommand.Connection.Open();
					}
					catch (Exception pEx)
					{
						EndExec(stbLog, stopWatch);

						Stop(pCommand, ProviderName, null);

						throw new DbGenericException(ProviderName, pCommand, pEx);
					}
					
					AddNotDefinedParametersAsNull(pCommand);
					AdjustParametersCommandQuestionMark(pCommand);

					sqlCommand = pCommand as SqlCommand;

					using (xmlReader = sqlCommand.ExecuteXmlReader ())
					{
						return pDelDataTransformer (xmlReader);
					}
				}
			}
			finally
			{
				EndExec(stbLog, stopWatch);

				Stop(pCommand, ProviderName, null);
			}
		}

		#endregion
		
		
		#region Parametros Derivados
		
		public void AddOrderByParameter (IDbCommand pCommand, string pName, params string [] pOrderByColmunNames)
		{
			if (DataHelper.IsEmpty (pOrderByColmunNames)) { return; }

			AddParameter (pCommand, pName, TextHelper.Concatenate (",", pOrderByColmunNames));
		}


		public  void AddTransformedParameterDerivedFromSQL (
		    IDbCommand pCommand, string pstrGetOneValueSQL, string pstrName, 
		    object pobjValue, bool pblnRequired, string pstrTitle)
		{
			AddTransformedParameterDerivedFromSQL (pCommand,
            	pstrGetOneValueSQL, pstrName, pobjValue, pblnRequired, pstrTitle, null);
		}
		
		
		public  void AddTransformedParameterDerivedFromSQL (
		    IDbCommand pCommand, string pstrGetOneValueSQL, string pstrName, 
		    object pobjValue, bool pblnRequired, string pstrTitle, 
		   	AddExtraParameters pdelExtraParameters)
		{
			IDbCommand dbcCommand;
			
			object objValue;
			
			
			dbcCommand = CreateCommand ();
			dbcCommand.CommandText = pstrGetOneValueSQL;
			
			AddParameter (dbcCommand, pstrName, pobjValue);
			
			if (pdelExtraParameters != null)
			{
				pdelExtraParameters (dbcCommand);
			}
			
			objValue = GetScalar<object> (dbcCommand);
			
			AddParameter (pCommand, pstrName, objValue);
		}

		
		public  void AddNotNullTransformedParameterDerivedFromSQL (IDbCommand pCommand, 
			string pstrGetOneValueSQL, string pstrName, object pobjValue, string pstrTitle)
		{
			AddNotNullTransformedParameterDerivedFromSQL (pCommand,
            	pstrGetOneValueSQL, pstrName, pobjValue, pstrTitle, null);
		}
		
		
		public  void AddNotNullTransformedParameterDerivedFromSQL (IDbCommand pCommand, 
			string pstrGetOneValueSQL, string pstrName, object pobjValue, string pstrTitle, AddExtraParameters pdelExtraParameters)
		{
			AddTransformedParameterDerivedFromSQL (pCommand,
            	pstrGetOneValueSQL, pstrName, pobjValue, true, pstrTitle, pdelExtraParameters);
		}
		
		#endregion


		#region Exportar XML

		public string ToXml(IDbCommand pCommand, string pstrTablaNombre, string pstrFilaNombre)
		{
			string strResultado;


			ToXml(pCommand, pstrTablaNombre, pstrFilaNombre, out strResultado);

			return strResultado;
		}


		public void ToXml(IDbCommand pCommand, string pstrTablaNombre, string pstrFilaNombre, out string pResult)
		{
			long intConteoRegistros;


			ToXml(pCommand, pstrTablaNombre, pstrFilaNombre, out pResult, out intConteoRegistros);
		}


		public void ToXml(IDbCommand pCommand, string pstrTablaNombre, string pstrFilaNombre, out string pResult, out long pRowCount)
		{
			StringBuilder stbContenedorXml;


			stbContenedorXml = new StringBuilder();

			ToXml(pCommand, pstrTablaNombre, pstrFilaNombre, new StringWriter(stbContenedorXml), 0, false, out pRowCount);

			pResult = stbContenedorXml.ToString();
		}


		public void ToXml(IDbCommand pCommand, string pstrTablaNombre, string pstrFilaNombre, string pstrRutaArchivo, ushort pshrFlushCadaXLinea, out long pRowCount)
		{
			try
			{
				ToXml(pCommand, pstrTablaNombre, pstrFilaNombre, File.CreateText(pstrRutaArchivo), pshrFlushCadaXLinea, false, out pRowCount);
			}
			catch (Exception excEx)
			{
				if (File.Exists(pstrRutaArchivo)) { File.Delete(pstrRutaArchivo); }
				throw new GenericException(GenericException.CRI_FILE_ERROR, string.Format("No pudo generarse el archivo en {0}", pstrRutaArchivo, excEx));
			}
		}


		private void ToXml(IDbCommand pCommand, string pstrTablaNombre, string pstrFilaNombre, TextWriter pobjTextWriter, ushort pshrFlushCadaXLinea, bool pblnUseXmlElementsData, out long pRowCount)
		{
			IDataReader dbrReader;

			ushort shrConteoRelativoLineaActual;

			int intI;

			XmlTextWriter xmlWriter;

			StringBuilder stbLog = new StringBuilder ();

			Stopwatch stopWatch = new Stopwatch();


			dbrReader = null;

			pCommand.CommandTimeout = 0;

			shrConteoRelativoLineaActual = 0;

			xmlWriter = new XmlTextWriter(pobjTextWriter);

			pRowCount = 0;


			try
			{
				RegCmdExec(stbLog, pCommand); 

				using (pCommand.Connection = CreateConnection ())
				{
					try
					{
						pCommand.Connection.Open ();
					}
					catch (Exception pEx)
					{
						EndExec(stbLog, stopWatch);

						Stop(pCommand, ProviderName, null);

						throw new DbGenericException(ProviderName, pCommand, pEx);
					}			
				
					AddNotDefinedParametersAsNull (pCommand);
					AdjustParametersCommandQuestionMark (pCommand);

					using (dbrReader = pCommand.ExecuteReader(CommandBehavior.CloseConnection))
					{
						xmlWriter.WriteStartElement(pstrTablaNombre);

						while (dbrReader.Read())
						{
							xmlWriter.WriteStartElement(pstrFilaNombre);

							for (intI = 0; intI < dbrReader.FieldCount; intI++)
							{
								if (!dbrReader.IsDBNull(intI))
								{
									if (pblnUseXmlElementsData)
									{
										xmlWriter.WriteElementString(dbrReader.GetName(intI), Convert.ToString(dbrReader[intI]));
									}
									else
									{
										xmlWriter.WriteAttributeString(dbrReader.GetName(intI), Convert.ToString(dbrReader[intI]));
									}
								}
							}

							xmlWriter.WriteEndElement();

							shrConteoRelativoLineaActual++;

							if (pshrFlushCadaXLinea > 0 && shrConteoRelativoLineaActual == pshrFlushCadaXLinea)
							{
								shrConteoRelativoLineaActual = 0;
								xmlWriter.Flush();
							}

							pRowCount++;
						}

						xmlWriter.WriteEndElement();
					}
				}
			}
			catch (Exception excEx)
			{
				throw new DbGenericException(ProviderName, pCommand, excEx);
			}
			finally
			{
				EndExec (stbLog, stopWatch);
				
				Stop(pCommand, ProviderName, null);

				if (xmlWriter != null) { xmlWriter.Close(); }
			}
		}

		#endregion


		#region Exportar CSV

		public void ToXSV(string pFilename, IDbCommand pCommand, string pSeparator, params string [] pColumns)
		{
			long rowCount;


			ToXSV (pFilename, pCommand, pSeparator, out rowCount, pColumns);
		}


		public void ToXSV(string pFilename, IDbCommand pCommand, string pSeparator,
		   out long pRowCount, params string [] pColumns)
		{
		    ToXSV (pFilename, pCommand, 0, 
				null, null, null, null, pSeparator.ToString (), true, null, out pRowCount, pColumns);
		}


		public void ToXSV(string pFilename, IDbCommand pCommand, ushort pshrFlushCadaXLinea, 
			string pHeader, string pRowStart, string pRowEnd, string pSepLeft, string pSepRight, bool pClearSepRightLastLine, string pFooter,
			out long pRowCount, params string [] pColumns)
		{ 
			FileHelper.Delete (pFilename);

			ToXSV(pCommand, File.CreateText(pFilename), pshrFlushCadaXLinea, 
				pHeader, pRowStart, pRowEnd, pSepLeft, pSepRight, pClearSepRightLastLine, pFooter,
				out pRowCount, pColumns);
		}


		public void ToXSV (IDbCommand pCommand, TextWriter pobjTextWriter, ushort pshrFlushCadaXLinea, 
			string pHeader, string pRowStart, string pRowEnd, string pSepLeft, string pSepRight, bool pClearSepRightLastLine, string pFooter,
			out long pRowCount, params string [] pColumns)
		{
			IDataReader dbrReader;

			ushort shrConteoRelativoLineaActual;

			int intI;

			int colIndex;

			bool found;

			bool [] colEnabledIndexes;

			List<string> columns;

			DataRowCollection columnsInfo;
			
			bool pAddLine;

			StringBuilder stbLog = new StringBuilder ();

			Stopwatch stopWatch = new Stopwatch ();


			dbrReader = null;

			pCommand.CommandTimeout = 0;

			shrConteoRelativoLineaActual = 0;

			pRowCount = 0;
			
			colEnabledIndexes = null;

			columns = null;

			pAddLine = false;


			if (string.IsNullOrEmpty (pSepLeft)) { pSepLeft = string.Empty; }
			if (string.IsNullOrEmpty (pSepRight)) { pSepRight = string.Empty; }
			if (string.IsNullOrEmpty (pRowEnd)) { pRowEnd = string.Empty; }

			try
			{
				RegCmdExec(stbLog, pCommand); 

				using (pCommand.Connection = CreateConnection ())
				{
					try
					{
						pCommand.Connection.Open ();
					}
					catch (Exception pEx)
					{
						EndExec(stbLog, stopWatch);

						Stop(pCommand, ProviderName, null);

						throw new DbGenericException(ProviderName, pCommand, pEx);
					}			
				
					AddNotDefinedParametersAsNull (pCommand);
					AdjustParametersCommandQuestionMark (pCommand);

					using (dbrReader = pCommand.ExecuteReader(CommandBehavior.CloseConnection))
					{						
						if (!string.IsNullOrEmpty (pHeader))
						{
							pobjTextWriter.Write (pHeader);

							pAddLine = true;
						}

						if (!string.IsNullOrEmpty (pRowStart))
						{
							if (pAddLine) { pobjTextWriter.WriteLine (); }

							pobjTextWriter.Write (pRowStart);

							pAddLine = true;
						}

						columnsInfo = dbrReader.GetSchemaTable().Rows;


						if (pColumns != null && pColumns.Length > 0)
						{
							columns = new List<string> (pColumns);

							colEnabledIndexes = new bool [dbrReader.GetSchemaTable().Rows.Count];

							intI = 0;

							colIndex = -1;
					
							// Verifica columnas
							foreach (string col in pColumns)
							{
								found = false;

								foreach (DataRow dtrFilaColInfo in columnsInfo)
								{	
									if (string.Compare (dtrFilaColInfo["BaseColumnName"].ToString(), col, true) == 0)
									{
										found = true;
									}
								}

								if (!found)
								{
									throw new GenericException (string.Format ("The column {0} is not part of the result of the query", col));
								}
							}
					
							if (pAddLine) { pobjTextWriter.WriteLine (); }
						
							foreach (DataRow dtrFilaColInfo in dbrReader.GetSchemaTable().Rows)
							{	
								colIndex ++;
						
								for (int i= 0; i < pColumns.Length - 1; i++)
								{
									if (string.Compare (dtrFilaColInfo["BaseColumnName"].ToString(), pColumns [i], true) == 0)
									{
										pobjTextWriter.Write(string.Format("{0}{1}{2}", 
											pSepLeft, 
											TextHelper.ReplaceAllRemoveLFLN (pColumns [i], pSepLeft, pSepRight), 
											pSepRight));
								
										colEnabledIndexes [colIndex] = true;
										break;
									}
								}
							}

							foreach (DataRow dtrFilaColInfo in dbrReader.GetSchemaTable().Rows)
							{	
								if (string.Compare (dtrFilaColInfo["BaseColumnName"].ToString(), pColumns [pColumns.Length - 1], true) == 0)
								{
									pobjTextWriter.Write(
										string.Format("{0}{1}", 
											pSepLeft,
											TextHelper.ReplaceAllRemoveLFLN (pColumns [pColumns.Length - 1], pSepLeft)));

									if (!pClearSepRightLastLine)
									{
										pobjTextWriter.Write(pSepRight);
									}

									colEnabledIndexes [colIndex] = true;
									break;
								}
							}
						}
						else
						{				
							if (pAddLine) { pobjTextWriter.WriteLine (); }

							for (int i = 0; i < columnsInfo.Count - 1; i++)
							{
								pobjTextWriter.Write(
									string.Format("{0}{1}{2}", 
										pSepLeft, 
										TextHelper.ReplaceAllRemoveLFLN (columnsInfo [i]["BaseColumnName"].ToString(), pSepLeft, pSepRight), 
										pSepRight));
							}

							pobjTextWriter.Write(
								string.Format("{0}{1}", 
									pSepLeft, 
									TextHelper.ReplaceAllRemoveLFLN (columnsInfo [columnsInfo.Count - 1] ["BaseColumnName"].ToString(), pSepLeft)));

							if (!pClearSepRightLastLine)
							{
								pobjTextWriter.Write(pSepRight);
							}
						}

						if (!string.IsNullOrEmpty (pRowEnd))
						{
							pobjTextWriter.Write (pRowEnd);
						}
				
						while (dbrReader.Read())
						{
							pobjTextWriter.WriteLine ();

							if (!string.IsNullOrEmpty (pRowStart))
							{
								pobjTextWriter.Write (pRowStart);
							}
				
							if (colEnabledIndexes == null)
							{					
								for (intI = 0; intI < dbrReader.FieldCount - 1; intI++)
								{
									pobjTextWriter.Write(pSepLeft);

									if (!dbrReader.IsDBNull(intI))
									{
										pobjTextWriter.Write(DataHelper.ToStringDbReaderItem (dbrReader, intI, Environment.NewLine, "\r"));
									}

									pobjTextWriter.Write(pSepRight);
								}

								pobjTextWriter.Write(pSepLeft);

								if (!dbrReader.IsDBNull(dbrReader.FieldCount - 1))
								{
									pobjTextWriter.Write(DataHelper.ToStringDbReaderItem (dbrReader, dbrReader.FieldCount - 1, Environment.NewLine, "\r"));
								}

								if (!pClearSepRightLastLine)
								{
									pobjTextWriter.Write(pSepRight);
								}
							}
							else
							{
								for (intI = 0; intI < dbrReader.FieldCount; intI++)
								{
									if (colEnabledIndexes [intI])
									{
										pobjTextWriter.Write(pSepLeft);

										if (!dbrReader.IsDBNull(intI))
										{
											pobjTextWriter.Write(DataHelper.ToStringDbReaderItem (dbrReader, intI, Environment.NewLine, "\r"));
										}

										if (intI == dbrReader.FieldCount - 1)
										{
											if (!pClearSepRightLastLine)
											{
												pobjTextWriter.Write(pSepRight);
											}
										}
										else
										{
											pobjTextWriter.Write(pSepRight);
										}
									}
								}
							}

							shrConteoRelativoLineaActual++;

							if (pshrFlushCadaXLinea > 0 && shrConteoRelativoLineaActual == pshrFlushCadaXLinea)
							{
								shrConteoRelativoLineaActual = 0;
								pobjTextWriter.Flush();
							}

							pRowCount++;
										
							pobjTextWriter.Write (pRowEnd);
						}

						if (!string.IsNullOrEmpty (pFooter))
						{
							pobjTextWriter.WriteLine ();
							pobjTextWriter.Write (pFooter);
						}
					}
				}
			}
			catch (Exception excEx)
			{
				throw new DbGenericException(ProviderName, pCommand, excEx);
			}
			finally
			{
				EndExec (stbLog, stopWatch);
				
				Stop(pCommand, ProviderName, null);

				if (pobjTextWriter != null) { pobjTextWriter.Close(); }
			}
		}

		#endregion


		#region Excel

		public void GetXLS (string pFilename, IDbCommand pCmd, params string [] pColumns)
		{
			GetXLS (pFilename, pCmd, 120, pColumns);
		}


		public void GetXLS (string pFilename, IDbCommand pCmd, int pColumnWidth, params string [] pColumns)
		{
			long rowCount;


			GetXLS (pFilename, pCmd, pColumnWidth, out rowCount, pColumns);
		}

		
		public void GetXLS (string pFilename, IDbCommand pCmd, int pColumnWidth, out long pRowCount, params string [] pColumns)
		{
			ToXSV (pFilename, pCmd, 0,  
				TextHelper.XLSHTMLHEADER, "<tr>", "</tr>",
				string.Format (@"<td width=""{0}"">", pColumnWidth), "</td>", false,
				"</table>", out pRowCount, pColumns);
		}

		#endregion
	}
}
